package lesson.student;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lesson.base.BaseService;
import lesson.base.paramresolve.UploadFile;
import lesson.clazz.ClazzService;
import lesson.db.DbUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.util.List;

public class StudentService  extends BaseService {


    private ClazzService clazzService = new ClazzService();

    /**
     * 保存年级
     * @param student
     */
    public String saveStudent(Student student) {

        if (isOk(student.getName()) == false) {
            return "学生姓名不可为空";
        }
        if (isOk(student.getSn()) == false) {
            return "学生学号不可为空";
        }
        if (isOk(student.getClassId()) == false) {
            return "学生班级不可为空";
        }

        if (checkExist(student)) {
            return "该学号已存在";
        }


        if (student.getId() == null || student.getId() == 0) {
            SqlSession session = DbUtil.getSession(true);
            try{

                StudentDao studentDao = session.getMapper(StudentDao.class);
                studentDao.addStudent(student);
            }finally {

                session.close();
            }

        } else {
            SqlSession session = DbUtil.getSession(true);
            try {
                StudentDao studentDao = session.getMapper(StudentDao.class);
                studentDao.updateStudent(student);

            }finally {

                session.close();
            }
        }
        return null;


    }


    /**
     * 检测叫这个名字的用户是否存在
     * @param student
     * @return
     */
    public boolean checkExist(Student student) {
        SqlSession session = DbUtil.getSession(true);
        try{
            StudentDao studentDao = session.getMapper(StudentDao.class);
            boolean exist = studentDao.checkExist(student);
            return exist;

        }finally {
            session.close();

        }
    }





    /**
     * 根据关键字查询
     * @param keywords
     * @return
     */
    public PageInfo<Student> listByPage(String keywords, Integer pageNumber, Integer pageSize) {
        SqlSession session = DbUtil.getSession(true);
        try{

            PageHelper.startPage(pageNumber, pageSize);
            List<Student> list = list(keywords, null, null);


            return new PageInfo<>(list);

        }finally {

            session.close();
        }
    }


    /**
     * 根据关键字查询
     * @param keywords
     * @param gradeId
     * @param classId
     * @return
     */
    public List<Student> list(String keywords, Integer gradeId, Integer classId) {
        SqlSession session = DbUtil.getSession(true);
        try{

            StudentDao studentDao = session.getMapper(StudentDao.class);
            List<Student> list = studentDao.list(keywords, gradeId, classId);


            return list;
        }finally {

            session.close();
        }

    }


    public String del(Integer id) {
        SqlSession session = DbUtil.getSession(true);
        try{
            StudentDao studentDao = session.getMapper(StudentDao.class);
            studentDao.delStudent(id);

            return null;

        }finally {
            session.close();

        }
    }

    public Student getById(Integer id) {
        SqlSession session = DbUtil.getSession(true);
        try{
            StudentDao studentDao = session.getMapper(StudentDao.class);
            Student student = studentDao.getById(id);
            return student;

        }finally {

            session.close();
        }
    }


    public Workbook export(String keywords) {
        List<Student> students = list(keywords, null, null);
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("学生数据");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("学生姓名");
        cell = row.createCell(1);
        cell.setCellValue("学生学号");
        cell = row.createCell(2);
        cell.setCellValue("学生年级");
        cell = row.createCell(3);
        cell.setCellValue("学生班级");
        cell = row.createCell(4);
        cell.setCellValue("学生性别");

        int rowIndex = 1;

        for (Student student : students) {
            row = sheet.createRow(rowIndex++);
            cell = row.createCell(0);
            cell.setCellValue(student.getName());
            cell = row.createCell(1);
            cell.setCellValue(student.getSn());
            cell = row.createCell(2);
            cell.setCellValue(student.getGradeName());
            cell = row.createCell(3);
            cell.setCellValue(student.getClassName());
            cell = row.createCell(4);
            cell.setCellValue(student.getSex());
        }

        return workbook;



    }



    public Workbook getDownloadModel() {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row headRow = sheet.createRow(0);
        Cell cell = headRow.createCell(0);
        cell.setCellValue("学生姓名");
        cell = headRow.createCell(1);
        cell.setCellValue("学生学号");
        cell = headRow.createCell(2);
        cell.setCellValue("学生年级");
        cell = headRow.createCell(3);
        cell.setCellValue("学生班级");
        cell = headRow.createCell(4);
        cell.setCellValue("学生性别");

        return workbook;
    }

    public String upload(UploadFile file) {
        // 开启一个事务
        SqlSession session = DbUtil.getSession(false);
        StudentDao studentDao = session.getMapper(StudentDao.class);
        String result = null;
        try {
            Workbook workbook = WorkbookFactory.create(new File(file.getAbsolutePath()));
            Sheet sheet = workbook.getSheetAt(0);
            int i=0;

            while (i < 60000) {
                Row row = sheet.getRow(i+1);
                if (row == null) {
                    break;
                }
                Student student = new Student();
                Cell cell = row.getCell(0);
                if (cell != null && cell.getStringCellValue().trim().equals("") == false) {
                    student.setName(cell.getStringCellValue().trim());
                } else {
                    result =  "第"+(i+2)+"行姓名不可为空";
                    break;
                }
                cell = row.getCell(1);
                if (cell != null && cell.getStringCellValue().trim().equals("") == false) {

                    student.setSn(cell.getStringCellValue().trim());
                    if (checkExist(student)) {
                        result = "第"+(i+2)+"行该学号已存在";
                        break;
                    }
                } else {
                    result= "第"+(i+2)+"行学号不可为空";
                    break;
                }
                cell = row.getCell(2);
                String gradeName = null;
                if (cell != null && cell.getStringCellValue().trim().equals("") == false) {
                    gradeName = cell.getStringCellValue().trim();
                } else {
                    result = "第"+(i+2)+"行年级名称不可为空";
                    break;
                }
                cell = row.getCell(3);
                String className = null;
                if (cell != null && cell.getStringCellValue().trim().equals("") == false) {
                    className = cell.getStringCellValue().trim();
                } else {
                    result = "第"+(i+2)+"行班级名称不可为空";
                    break;

                }
                Integer classId = clazzService.getClassIdByNames(gradeName, className);
                if (classId == null) {
                    result = "第"+(i+2)+"行年级名称和班级名称填写有误";
                    break;
                }
                student.setClassId(classId);
                cell = row.getCell(4);
                if (cell != null && cell.getStringCellValue().trim().equals("") == false) {
                    student.setSex(cell.getStringCellValue().trim());
                }

                studentDao.addStudent(student);

                i++;
            }

            if (result == null) {
                session.commit();
            } else {
                session.rollback();
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
        return result;

    }
}
